/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import java.sql.CallableStatement;
import Entity.Etbl_Cities;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author LeThuyLinh
 */
public class DALtbl_Cities extends BaseConnection{
    CallableStatement cstm=null;
    ResultSet rs=null;
    
    public boolean Addtbl_City(Etbl_Cities obj){
        try {
            getConnection();
            cstm=cnn.prepareCall("{call tbl_Cites_add(?,?,?)}");
            cstm.setString("name", obj.getName());
            cstm.setString("description", obj.getDescription());
            cstm.setInt("country_Id", obj.getCountry_ID());
            cstm.execute();
            cstm.close();
            releaseConnection();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Cities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public Etbl_Cities getCity(int cityID){
        Etbl_Cities objCity=null;
        try {
            getConnection();
            cstm=cnn.prepareCall("{call tbl_Cites_get(?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            cstm.setInt("cityId", cityID);
            rs=cstm.executeQuery();
            while(rs.next()){
                objCity=new Etbl_Cities();
                objCity.setCityID(rs.getInt("cityId"));
                objCity.setName(rs.getString("name"));
                objCity.setDescription(rs.getString("description"));
                objCity.setCountry_ID(rs.getInt("country_Id"));
                objCity.setCountryName(rs.getString("country_name"));
                return objCity;
            }
            cstm.close();
            rs.close();
            releaseConnection();
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Cities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean updatetbl_City(Etbl_Cities obj){
        try {
            getConnection();
            cstm=cnn.prepareCall("{call tbl_Cites_update(?,?,?,?)}");
            cstm.setInt("cityId", obj.getCityID());
            cstm.setString("name", obj.getName());
            cstm.setString("description", obj.getDescription());
            cstm.setInt("country_Id", obj.getCountry_ID());
            cstm.execute();
            cstm.close();
            releaseConnection();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Cities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public boolean deletetbl_City(int cityID){
        try {
            getConnection();
            cstm=cnn.prepareCall("{call tbl_Cites_delete(?)}");
            cstm.setInt("cityId", cityID);
            cstm.execute();
            cstm.close();
            releaseConnection();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Cities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public ArrayList getListCity(String orderBy, String ASCorDESC, String WhereClause, int PagingIndex, int PagingSize){
        try {
            ArrayList CitiesList=new ArrayList();
            getConnection();
            cstm=cnn.prepareCall("{call tbl_Cites_getpaged(?,?,?,?,?)}");
            cstm.setString("OrderBy", orderBy);
            cstm.setString("TangGiam", ASCorDESC);
            cstm.setString("WhereClause", WhereClause);
            cstm.setInt("PageIndex", PagingIndex);
            cstm.setInt("PageSize", PagingSize);
            rs=cstm.executeQuery();
            DALTotalPageIndex.getTotalPage("tbl_Cites", WhereClause, PagingSize);
            while(rs.next()){
                Etbl_Cities objCity=new Etbl_Cities();
                objCity.setCityID(rs.getInt("cityId"));
                objCity.setName(rs.getString("city_name"));
                objCity.setDescription(rs.getString("description"));
                objCity.setCountry_ID(rs.getInt("country_Id"));
                objCity.setCountryName(rs.getString("country_name"));
                CitiesList.add(objCity);
            }
            cstm.close();
            rs.close();
            releaseConnection();
            return CitiesList;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Cities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
}
